﻿using System;
using System.Collections.Generic;
using System.Text;
using org.in2bits.MyXls;
using org.in2bits.MyXls.ByteUtil;

namespace emp.Utility
{
    public static class ExcelSaveFile
    {
        public static string ExcelSaves(System.Data.DataTable dt, string[] teRows, string pathName, string tableName)
        {

            if (teRows.Length == 0 || teRows == null || dt == null || dt.Rows.Count == 0 || pathName == "" || tableName == "")
            {
                emp.Utility.LogsUtil.DoLog("调用参数为空！/r/n");
                return null;
            }
            if (teRows.Length != dt.Columns.Count)
            {
                emp.Utility.LogsUtil.DoLog("储存Excel表格列数与导入的DataTable列数不一致！/r/n");
                return null;
            }
            string newPath = tableName + ".xls";

            //当物理路径下不存在同名文件才保存
            if (System.IO.File.Exists(pathName +""+ newPath))
            {
                newPath = tableName + CreateRandom() + ".xls";
            }
            
            try
            {
                XlsDocument doc = new XlsDocument();
                doc.FileName = newPath;
                
                Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
                Cell cell = null;
                try
                {
                    //生成表头
                    for (int i = 0; i < teRows.Length; i++)
                    {
                        cell = sheet.Cells.Add(1, i+1, teRows[i].ToString());
                    }

                    //生成表数据
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {

                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (dt.Rows[i][j] != null && dt.Rows[i][j].ToString()!="")
                            {
                                cell = sheet.Cells.Add(i + 2, j + 1, dt.Rows[i][j].ToString());

                            }
                            

                        }


                    }

                }
                catch (Exception ex)
                {
                    emp.Utility.LogsUtil.DoLog("储存Excel表格数据出错!插入数据格式或内容有误！/r/n");
                    emp.Utility.LogsUtil.DoLog(ex.ToString());
                    return null;
                    throw;

                }
                try
                {
             
                    doc.Save(pathName);

                }
                catch (Exception ex)
                {
                    emp.Utility.LogsUtil.DoLog("保存Excel表格出错!/r/n");
                    emp.Utility.LogsUtil.DoLog(ex.ToString());
                    return null;
                    throw;
                }

                

            }
            catch (Exception ex)
            {
                emp.Utility.LogsUtil.DoLog("创建Excel表格出错!/r/n");
                emp.Utility.LogsUtil.DoLog(ex.ToString());
                return null;
                throw;
            }


            return newPath;
        }

        //生成1-6位随机数
        public static string CreateRandom()
        {
            Random random = new Random();
            int i = random.Next(1, 999999);
            return i.ToString();
        }
        //生成5位随机数
        public static string CreateRandom5()
        {
            Random random = new Random();
            int i = random.Next(10000, 99999);
            return i.ToString();
        }


        public static string ExcelSaves(System.Data.DataTable dt, string pathName)
        {

            if (dt == null || dt.Rows.Count == 0 || pathName == "")
            {
                TraceLog.RecordLog("调用参数为空！/r/n");
                return null;
            }


            try
            {
                XlsDocument doc = new XlsDocument();
                doc.FileName = pathName;

                Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
                Cell cell = null;
                try
                {

                    for (int j = 0; j < dt.Columns.Count; j++)
                    {
                        cell = sheet.Cells.Add(1, j + 1, dt.Columns[j].ColumnName);

                    }

                    //生成表数据
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {

                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (dt.Rows[i][j] != null && dt.Rows[i][j].ToString() != "")
                            {
                                cell = sheet.Cells.Add(i + 2, j + 1, dt.Rows[i][j].ToString());

                            }


                        }


                    }

                }
                catch (Exception ex)
                {
                    TraceLog.RecordLog("储存Excel表格数据出错!插入数据格式或内容有误！/r/n");
                    TraceLog.RecordLog(ex.ToString());
                    return null;
                    throw;

                }
                try
                {
                    doc.Save(pathName, true);

                }
                catch (Exception ex)
                {
                    TraceLog.RecordLog("保存Excel表格出错!/r/n");
                    TraceLog.RecordLog(ex.ToString());
                    return null;
                    throw;
                }



            }
            catch (Exception ex)
            {
                TraceLog.RecordLog("创建Excel表格出错!/r/n");
                TraceLog.RecordLog(ex.ToString());
                return null;
                throw;
            }


            return pathName;
        }


    }
}
